package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import pojo.Product;
import util.DataConn;

public class ProductDao {
	public int updateQuantity(String pid,int quantity) {
		Connection conn=DataConn.getConn();
		Product p =this.getOneProduct(pid);
		String sql="update product set p_quantity=? where p_id=?";
		int flag=0;
		try {
			PreparedStatement pst=conn.prepareStatement(sql);
			pst.setInt(1, (p.getP_quantity()-quantity));
			pst.setString(2, pid);
			flag=pst.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}
	public int getTotal() {
		int flag=0;
		Connection conn=DataConn.getConn();
		String sql="select count(*) as total from product";
		try {
			PreparedStatement ps=conn.prepareStatement(sql);
			ResultSet rs=ps.executeQuery();
			if(rs.next()) {
				flag=rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}
	public int getMaxPage(int pageSize) {
		int maxPage=0;
		int count=this.getTotal();//获取总行数
		maxPage = (count%pageSize==0)?(count/pageSize):(count/pageSize+1);
		//if(count%pageSize==0) {
			//maxPage=count/pageSize;
		//}else {
			//maxPage=count/pageSize+1;
		//}
		return maxPage;
	}
	
	public List<Product> PageData(int currentPage,int pageSize){
		List<Product> List=new ArrayList<Product>();
		Connection conn=DataConn.getConn();
		String sql="select * from product";
		try {
			PreparedStatement ps = conn.prepareStatement(sql,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			ResultSet rs = ps.executeQuery();
			Product p=null;
			//使指针移到第几页的第一条数据
			rs.absolute((currentPage-1)*pageSize+1);
			for(int i =0;i<pageSize;i++) {//循环pageSize次
				p = new Product();
				p.setP_type(rs.getString("p_type"));
				p.setP_id(rs.getString("p_id"));
				p.setP_name(rs.getString("p_name"));
				p.setP_price(rs.getFloat("p_price"));
				p.setP_quantity(rs.getInt("p_quantity"));
				p.setP_image(rs.getString("p_image"));
				p.setP_description(rs.getString("p_description"));
				p.setP_time(rs.getString("p_time"));
				List.add(p);
			if(!rs.next()) {
				break;
			};
		}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return List;
	}
	public int del(String p_id) {
		int flag=0;
		Connection conn=DataConn.getConn();
		String sql="delete from product where p_id=?";
		try {
			PreparedStatement ps=conn.prepareStatement(sql);
			ps.setString(1, p_id);
			flag=ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}
	public int update(Product p) {
		int flag=0;
		Connection conn = DataConn.getConn();
		String sql="update product set p_name=?,p_quantity=?,p_image=? where p_id=?";
		PreparedStatement ps;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, p.getP_name());
			ps.setInt(2, p.getP_quantity());//原有的数量上加一
			ps.setString(3, p.getP_image());
			ps.setString(4, p.getP_id());
			flag = ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}
	public Product getOneProduct(String pid) {
		 Product p = null;
		 Connection conn = DataConn.getConn();
			String sql = "select * from product where p_id=?";  
			try {
				PreparedStatement pst = conn.prepareStatement(sql);
				pst.setString(1,pid);
				ResultSet rs = pst.executeQuery();
				if(rs.next()) {
					p = new Product();
					p.setP_type(rs.getString("p_type"));
					p.setP_id(rs.getString("p_id"));
					p.setP_name(rs.getString("p_name"));
					p.setP_price(rs.getFloat("p_price"));
					p.setP_quantity(rs.getInt("p_quantity"));
					p.setP_image(rs.getString("p_image"));
					p.setP_description(rs.getString("p_description"));
					p.setP_time(rs.getString("p_time"));
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		 return p;
	}
	public int add(Product p) {
		PreparedStatement ps;
		int flag = 0;
		Connection conn = DataConn.getConn();
		String sql="insert into product values(?,?,?,?,?,?,?,?)";
		try {
			ps = conn.prepareStatement(sql);
				PreparedStatement pst=conn.prepareStatement(sql);
				pst.setString(1, p.getP_type());
				pst.setString(2, p.getP_id());
				pst.setString(3, p.getP_name());
				pst.setFloat(4, p.getP_price());
				pst.setInt(5, p.getP_quantity());
				pst.setString(6, p.getP_image());
				pst.setString(7, p.getP_description());
				pst.setString(8, p.getP_time());
				flag=pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}
	public List<Product> showProductByType(int currentPage,int pageSize,String type){
		List<Product> List=new ArrayList<Product>();
		Connection conn=DataConn.getConn();
		String sql="select * from product where p_type=?";
		try {
			PreparedStatement ps = conn.prepareStatement(sql,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			ps.setString(1, type);
			ResultSet rs = ps.executeQuery();
			Product p=null;
			//使指针移到第几页的第一条数据
			rs.absolute((currentPage-1)*pageSize+1);
			for(int i =0;i<pageSize;i++) {//循环pageSize次
				p = new Product();
				p.setP_type(rs.getString("p_type"));
				p.setP_id(rs.getString("p_id"));
				p.setP_name(rs.getString("p_name"));
				p.setP_price(rs.getFloat("p_price"));
				p.setP_quantity(rs.getInt("p_quantity"));
				p.setP_image(rs.getString("p_image"));
				p.setP_description(rs.getString("p_description"));
				p.setP_time(rs.getString("p_time"));
				List.add(p);
			if(!rs.next()) {
				break;
			};
		}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return List;
	}
}
